package com.cheney.demo.data;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.util.*;

/**
 * @author si.chen
 * @date 2024/12/22 16:58
 */
//用Java，实现如图的转换，输入文件来自D:/tmp/import4.xlsx，输出文件保存在D:/tmp/export4.xlsx，输入的空字符串也要识别,结果按照a列排序，重复数是相同Key-Value出现的次数
public class KV去重_04 {
    public static void main(String[] args) throws IOException {
        // 读取输入Excel文件
        List<String[]> inputData = readExcelFile("D:/tmp/import4.xlsx");
        
        // 使用Map存储每个key对应的所有value
        Map<String, Set<String>> keyValueMap = new HashMap<>();
        // 统计每个key-value对的出现次数
        Map<String, Map<String, Integer>> pairCountMap = new HashMap<>();
        // 存储key-value对的列表，用于排序
        List<String[]> uniquePairs = new ArrayList<>();
        
        // 处理数据
        for (String[] row : inputData) {
            String key = row[0];
            String value = row[1];
            
            // 收集每个key对应的所有value
            keyValueMap.computeIfAbsent(key, k -> new HashSet<>()).add(value);
            
            // 统计key-value对的出现次数
            pairCountMap.computeIfAbsent(key, k -> new HashMap<>())
                       .merge(value, 1, Integer::sum);
            
            // 如果这个key-value对还没有记录，则添加到列表中
            if (uniquePairs.stream().noneMatch(pair -> 
                pair[0].equals(key) && pair[1].equals(value))) {
                uniquePairs.add(new String[]{key, value});
            }
        }
        
        // 按a列（key）排序
        uniquePairs.sort((pair1, pair2) -> {
            int keyCompare = pair1[0].compareTo(pair2[0]);
            return keyCompare != 0 ? keyCompare : pair1[1].compareTo(pair2[1]);
        });
        
        // 创建新的工作簿
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("Result");
        
        // 创建黄色背景样式
        CellStyle yellowStyle = workbook.createCellStyle();
        yellowStyle.setFillForegroundColor(IndexedColors.YELLOW.getIndex());
        yellowStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        
        // 写入标题行
        Row headerRow = sheet.createRow(0);
        headerRow.createCell(0).setCellValue("a");
        headerRow.createCell(1).setCellValue("b");
        headerRow.createCell(2).setCellValue("重复数");
        headerRow.createCell(3).setCellValue("存在同Key不同Value");
        
        // 写入数据
        int rowIndex = 1;
        for (String[] pair : uniquePairs) {
            String key = pair[0];
            String value = pair[1];
            Row row = sheet.createRow(rowIndex++);
            
            // 判断是否存在同Key不同Value
            boolean hasDifferentValues = keyValueMap.get(key).size() > 1;
            
            // 设置单元格值和样式
            Cell cell0 = row.createCell(0);
            Cell cell1 = row.createCell(1);
            Cell cell2 = row.createCell(2);
            Cell cell3 = row.createCell(3);
            
            cell0.setCellValue(key);
            cell1.setCellValue(value);
            // 获取当前key-value对的出现次数
            cell2.setCellValue(pairCountMap.get(key).get(value));
            cell3.setCellValue(hasDifferentValues ? "Y" : "N");
            
            // 如果存在同Key不同Value，设置黄色背景
            if (hasDifferentValues) {
                cell0.setCellStyle(yellowStyle);
                cell1.setCellStyle(yellowStyle);
                cell2.setCellStyle(yellowStyle);
                cell3.setCellStyle(yellowStyle);
            }
        }
        
        // 自动调整列宽
        for (int i = 0; i < 4; i++) {
            sheet.autoSizeColumn(i);
        }
        
        // 保存文件
        try (FileOutputStream fileOut = new FileOutputStream("D:/tmp/export4.xlsx")) {
            workbook.write(fileOut);
        }
        workbook.close();
    }
    
    private static List<String[]> readExcelFile(String filePath) throws IOException {
        List<String[]> data = new ArrayList<>();
        try (FileInputStream inputStream = new FileInputStream(new File(filePath));
             Workbook workbook = new XSSFWorkbook(inputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            // 跳过标题行
            for (int i = 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row != null) {
                    String[] rowData = new String[2];
                    for (int j = 0; j < 2; j++) {
                        Cell cell = row.getCell(j, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                        if (cell.getCellType() == CellType.BLANK) {
                            rowData[j] = "";
                        } else if (cell.getCellType() == CellType.NUMERIC) {
                            rowData[j] = String.format("%.0f", cell.getNumericCellValue());
                        } else {
                            rowData[j] = cell.toString().trim();
                        }
                    }
                    data.add(rowData);
                }
            }
        }
        return data;
    }
}
